from flask import Blueprint, request
from utilsapp.mysql_con import db_connect

set_ = Blueprint('set', __name__)


# 查询device列表
@set_.route('/device/getlist', methods=["POST"])
def get_device_list():
    size = int(request.json.get('size'))
    page = int(request.json.get('page'))
    scope_id = 0 if not request.json.get(
        'scope_id') else request.json.get('scope_id')
    type_sql = f"""SELECT type FROM regionlevel where id = {scope_id} """
    device_name = 0 if not request.json.get(
        "device_name") else request.json.get("device_name")
    whe = ""
    type_ = ''
    if scope_id != 0:
        type_ = db_connect(type_sql)["data"][0]["type"]
        whe = f"""and d.{type_}_id = {scope_id}"""
    else:
        whe = ''
    sql = f"""select d.id,d.device_name,t.name as type_name,d.type_id,d.device_img as imgurl,d.`status`,g.name as group_name ,c.name as 
                company_name,f.name as factory_name,z.name as zone_name,tb.c sensor_count as db_name
                from device d
                LEFT JOIN regionlevel z on z.id=d.zone_id
                LEFT JOIN regionlevel f on f.id=d.factory_id
                LEFT JOIN regionlevel c on c.id=d.company_id
                LEFT JOIN regionlevel g on g.id=d.group_id
                LEFT JOIN device_type t on t.id=d.type_id
                LEFT JOIN (SELECT COUNT(id) c ,device_id FROM sensor GROUP BY device_id ) tb on tb.device_id=d.id
                WHERE 1=1 and IF('{device_name}'='0' ,""="",d.device_name like '%{device_name}%')
            {whe}  LIMIT {max(0, (page - 1) * size)},{size}"""
    sql_1 = f"""select count(*) as num from device d,regionlevel z,regionlevel f,regionlevel c,regionlevel g ,device_type t
            where d.zone_id=z.id and d.factory_id=f.id and d.company_id=c.id and d.group_id=g.id and 
            t.id=d.type_id and IF('{device_name}'='0' ,""="",d.device_name like '%{device_name}%')
            {whe} """

    total_num = db_connect(sql_1)
    total = total_num['data'][0]['num']
    res = db_connect(sql)

    if res['code'] == 0:
        return {
            "code": 0,
            "data": res['data'],
            "pagination": {
                "page": page,
                "pageSize": size,
                "total": total,
            },
        }
    else:
        return res
